-- lists all jobs on the current server with counts of job steps by type
-- sp_helplogins
USE msdb;
SELECT
	ServerName		= @@SERVERNAME,
	Job_Name		= j.name,
	Job_Owner		= lj.name,
	Job_Description	= NULLIF(j.description, 'No description available.'),
	Job_Enabled		= j.enabled,
	Schedule_Name	= s.name,
	Schedule_Enabled= s.enabled,
	Schedule_Owner	= ls.name,
	Total_Steps		= (SELECT COUNT(*) FROM sysjobsteps WHERE job_id = j.job_id),
	SSIS_Steps		= (SELECT COUNT(*) FROM sysjobsteps WHERE job_id = j.job_id AND (subsystem = 'SSIS' OR (subsystem = 'CmdExec' AND command LIKE '%DTSRun%'))),
	SMTP_Steps		= (SELECT COUNT(*) FROM sysjobsteps WHERE job_id = j.job_id AND  subsystem = 'TSQL' AND command LIKE '%sp_SQLSMTPMail%')	-- sp_send_dbmail
  FROM msdb.dbo.sysjobs j
	LEFT JOIN msdb.dbo.sysjobschedules js ON j.job_id = js.job_id
	INNER JOIN msdb.dbo.sysschedules s ON js.schedule_id = s.schedule_id
	LEFT JOIN master.sys.syslogins lj ON j.owner_sid = lj.sid
	LEFT JOIN master.sys.syslogins ls ON s.owner_sid = ls.sid
  ORDER BY j.name
GO